import mssql
import config
from typing import Dict
import time
import base64
import hmac

# 数据查询
def query(sql):
    db = mssql.ZgqMssql(config.get('db'))
    dataList = db.query(sql)
    db.close()
    return dataList

# 生成token
def generateToken(username: str, expire=3600):
    expireTime = time.time() + expire
    ts_str = str(expireTime)
    ts_byte = ts_str.encode("utf-8")
    sha1_tshexstr  = hmac.new(username.encode("utf-8"),ts_byte,'sha1').hexdigest() 
    token = ts_str+':'+sha1_tshexstr
    b64_token = base64.urlsafe_b64encode(token.encode("utf-8"))
    token = b64_token.decode("utf-8")
    # 打开数据库连接
    db = mssql.ZgqMssql(config.get('db'))
    # 先删除原先的token
    sql = 'delete from user_token where username = \'' + username + '\''
    db.execute(sql)
    # 把token保存至数据库    
    saveData = { "username": username, "expire": expireTime, "token": token }
    sql = db.add('user_token', saveData, 'insert', False)
    # 提交数据
    db.commit()
    # 关闭数据库连接
    db.close()
    return token

# 验证token    
def certifyToken(token):
    username = ''
    name = ''
    sql = 'select convert(nvarchar,user_token.username) username,' + \
        '\nconvert(nvarchar,staff_member.name) name,' + \
        '\nstaff_member.country_code country_code,' + \
        '\n\'01\' cert_type,' + \
        '\nwh_ysxx.sfzh certno' + \
        '\nfrom user_token inner join staff_member on user_token.username = staff_member.staff_nbr' + \
        '\ninner join wh_ysxx on staff_member.staff_id = wh_ysxx.staff_id' + \
        '\nWHERE token = \'' + token + '\' and expire > ' + str(time.time())
    list = query(sql)
    result = len(list)==1
    ret = {}
    if result:
        username = list[0]['username']
        name = list[0]['name']
        
        sql = 'update user_token set expire = ' + str(time.time() + 3600) + ' where token = \'' + token + '\''
        db = mssql.ZgqMssql(config.get('db'))
        db.execute(sql)
        db.commit()
        db.close()
    else:
        ret['code'] = 201
        ret['msg'] = 'token过期或不存在'
    returnData = list[0]
    returnData['result'] = result
    returnData['ret'] = ret
    return returnData #{ "result": result, "username": username, "name": name, "ret": ret }

def userLogin(loginData: Dict):
    sql = 'SELECT staff_id FROM staff_member WHERE STAFF_NBR = \'' + loginData.get('username') + '\' AND PASSWORD = \'' + loginData.get('password') + '\''
    list = query(sql)
    if len(list) == 1:
        ret = {
            "code": 200,
            "msg": '登录成功',
            "data": {
                "token": generateToken(loginData.get('username'))
            }
        }
    else:
        ret = {
            "code": 201,
            "msg": '登录失败，用户名或密码不正确'
        }
    return ret

def userInfo(username: str):
    sql = 'SELECT convert(int,staff_id) staff_id,staff_nbr,convert(int,staff_member.sect_id) sect_id,' + \
        '\nconvert(NVARCHAR(1000),staff_member.name) name,convert(nvarchar(1000),sect.name) sect_name,' + \
        '\nstaff_member.country_code' + \
        '\nFROM staff_member INNER JOIN sect ON staff_member.sect_id = sect.sect_id ' + \
        '\nWHERE STAFF_NBR = \'' + username + '\''
    list = query(sql)
    if len(list) == 1:
        return {
            "code": 200,
            "msg": '成功',
            "data": {
                "roles": ["admin"],
                "introduction": "",
                "avatar": "https://wpimg.wallstcn.com/f778738c-e4f8-4870-b634-56703b4acafe.gif",
                "name": list[0]['name'],
                "userInfo": list[0]
            }
        }
    else:
        return {
            "code": 201,
            "msg": '用户不存在',
         }

def userLogout(username: str, token: str):
    sql = 'delete from user_token where username = \'' + username + '\' and token = \'' + token + '\''
    db = mssql.ZgqMssql(config.get('db'))
    rowcount = db.execute(sql)
    db.commit()
    db.close()
    return {
        "code": 200,
        "msg": 'success',
        "data": {}
    }
